﻿using LFramework.Data;
using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;

namespace DataServices
{
    /// <summary>
    /// 艺术品评论DB层
    /// </summary>
    public class DaoArtWorkReview
    {
        IDBHelper dbhelper = DBFactory.CreateFromConfig("conn");


        public int LoadArtWorkCount()
        {
            string sql = "select count(*) as iCount from tbArtWorkReview";

            return Convert.ToInt32(dbhelper.ExecuteScalar(sql));
        }
        public void AddArtWorkReview(ArtWorkReviewInfo artWorkReview)
        {
            string sql = @"insert into tbArtWorkReview(
                            [ID]
                           ,[Title]
                           ,[Reviewer]
                           ,[ArtWorkReview]
                           ,[AddTime]) 
                           values (
                            @ID,
                            @Title,
                            @Reviewer,
                            @ArtWorkReview,
                            @AddTime
                            )  
                           ";

            OleDbParameter[] paras = { new OleDbParameter("ID",artWorkReview.ID),
                                         new OleDbParameter("Title",artWorkReview.Title),
                                         new OleDbParameter("Reviewer",artWorkReview.Reviewer),
                                     new OleDbParameter("ArtWorkReview", artWorkReview.ArtWorkReview),
                                      new OleDbParameter("AddTime", DateTime.Now.ToString("yyyy-MM-dd"))
                                   };

            dbhelper.ExecuteSQL_InTrans(sql, paras);
        }

        public void DeleteArtWorkReview(string id)
        {
            string sql = "";
            sql = "delete from tbArtWorkReview where ID = '" + id + "'";
            dbhelper.ExecuteSQL(sql);
        }

        public void UpdateArtWorkReview(ArtWorkReviewInfo artWorkReview)
        {
            string sql = @"
update tbArtWorkReview 
set Title=@Title,Reviewer = @Reviewer , ArtWorkReview = @ArtWorkReview
where ID = '" + artWorkReview.ID + "'";

            OleDbParameter[] paras = {  new OleDbParameter("Title", artWorkReview.Title) , 
                                         new OleDbParameter("Reviewer", artWorkReview.Reviewer) , 
                                       new OleDbParameter("ArtWorkReview",artWorkReview.ArtWorkReview),
                                   };
            dbhelper.ExecuteSQL(sql, paras);
        }

        public IList<ArtWorkReviewInfo> LoadArtWorkReviewByPage(int page, int pageSize)
        {
            IList<ArtWorkReviewInfo> list = new List<ArtWorkReviewInfo>();

            string sql = null;
            if (page != 1)
            {
                int aNum = page * pageSize;

                int recBegin = pageSize * (page - 1);

                sql = @"
                           
select top " + pageSize + @" ID ,Title ,AddTime 
from (select ID ,Title ,AddTime from  tbArtWorkReview 
where ID not in(select top " + recBegin + @" ID from (select ID from tbArtWorkReview order by AddTime desc) as tableresult)  
order by AddTime desc) as tabler
    
                          ";
            }
            else
            {
                sql = "select top " + page * pageSize + "  ID ,Title ,AddTime from(select ID ,Title ,AddTime from  tbArtWorkReview order by AddTime desc) as tabresult";
            }

            DataTable dt = dbhelper.QueryForDataTable(sql);

            foreach (DataRow dr in dt.Rows)
            {
                ArtWorkReviewInfo artworkreview = new ArtWorkReviewInfo()
                {
                    ID = dr["ID"].ToString(),
                    Title = dr["Title"].ToString(),
                    AddTime = dr["AddTime"].ToString()
                };

                list.Add(artworkreview);
            }

            return list;
        }

        public ArtWorkReviewInfo LoadArtWorkReviewById(string id)
        {
            string sql = "select * from tbArtWorkReview where ID = '" + id + "'";

            DataRow dr = dbhelper.QueryForDataTable(sql).Rows[0];

            ArtWorkReviewInfo artworkreview = new ArtWorkReviewInfo()
            {
                ID = dr["ID"].ToString(),
                Title = dr["Title"].ToString(),
                Reviewer=dr["Reviewer"].ToString(),
                AddTime = dr["AddTime"].ToString(),
                ArtWorkReview = dr["ArtWorkReview"].ToString()
            };
            return artworkreview;
        }
    }
}
